package database;

import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import object.TSM_Dep;
import object.TotalSalaryMonth;
import object.TotalSalaryQuarter;
import object.TotalSalaryYear;
import clientMain.Login;
import clientMain.Main;

public class TotalSalaryDelegate {
	
	/**
	 * @author Tidus
	 * @param myStart, myEnd: MonthYear Start, MonthYear End [mm/yyyy]
	 * @param flag [true]: interval, [false]: between
	 * @return
	 */
	public Vector<TotalSalaryMonth> getTSM(String myStart, String myEnd, boolean flag){
		Vector<TotalSalaryMonth> list = new Vector<TotalSalaryMonth>();
		try {
			Login.conn.setAutoCommit(false);
			CallableStatement cst = Login.conn.prepareCall("{call sp_ListTSM(?,?,?,?)}");
			cst.setInt(1, Main.comId);
			cst.setString(2, myStart);
			cst.setString(3, myEnd);
			cst.setBoolean(4, flag);
			ResultSet rs = cst.executeQuery();
			while(rs.next()){
				TotalSalaryMonth tsm = new TotalSalaryMonth();
				tsm.setMonthYear(rs.getString("MonthYear"));
				tsm.setTotalSalary(rs.getInt("TotalSalary"));
				list.add(tsm);
			}
			Login.conn.commit();
		} catch (SQLException e) {
			list = null;
			e.printStackTrace();
		}
		return list;
	}
	
	/**
	 * @author Tidus
	 * @param comId : comId != 0 return TSM_company 
	 * @param listDepId [x,y,z,...] x,y,z: int
	 * @param myStart, myEnd: MonthYear Start, MonthYear End [mm/yyyy]
	 * @param flag [true]: interval, [false]: between
	 * @param flag
	 * @return
	 */
	public Vector<TSM_Dep> getTSM_Dep(int comId, String listDepId, String myStart, String myEnd, boolean flag){
		Vector<TSM_Dep> list = new Vector<TSM_Dep>();
		try {
			Login.conn.setAutoCommit(false);
			CallableStatement cst = Login.conn.prepareCall("{call sp_ListTSM_Dep(?,?,?,?,?)}");
			cst.setInt(1, comId);
			cst.setString(2, listDepId);
			cst.setString(3, myStart);
			cst.setString(4, myEnd);
			cst.setBoolean(5, flag);
			ResultSet rs = cst.executeQuery();
			while(rs.next()){
				TSM_Dep tsm = new TSM_Dep();
				tsm.setMonthYear(rs.getString("MonthYear"));
				tsm.setDepId(rs.getInt("DepId"));
				tsm.setTotalSalary(rs.getInt("TotalSalary"));
				list.add(tsm);
			}
			Login.conn.commit();
		} catch (SQLException e) {
			list = null;
			e.printStackTrace();
		}
		return list;
	}
	
	/**
	 * @author Tidus
	 * @param qyStart, qyEnd: QuaterYear Start, QuaterYear End [qqq/yyyy]
	 * @param flag [true]: interval, [false]: between
	 * @return
	 */
	public Vector<TotalSalaryQuarter> getTSQ(String qyStart, String qyEnd, boolean flag){
		Vector<TotalSalaryQuarter> list = new Vector<TotalSalaryQuarter>();
		try {
			Login.conn.setAutoCommit(false);
			CallableStatement cst = Login.conn.prepareCall("{call sp_ListTSQ(?,?,?,?)}");
			cst.setInt(1, Main.comId);
			cst.setString(2, qyStart);
			cst.setString(3, qyEnd);
			cst.setBoolean(4, flag);
			ResultSet rs = cst.executeQuery();
			while(rs.next()){
				TotalSalaryQuarter tsq = new TotalSalaryQuarter();
				tsq.setQuarterYear(rs.getString("QuarterYear"));
				tsq.setTotalSalary(rs.getInt("TotalSalary"));
				list.add(tsq);
			}
			Login.conn.commit();
		} catch (SQLException e) {
			list = null;
			e.printStackTrace();
		}
		return list;
	}
	
	/**
	 * @author Tidus
	 * @param yearStart, yearEnd [yyyy]
	 * @param flag [true]: interval, [false]: between
	 * @return
	 */
	public Vector<TotalSalaryYear> getTSY(String yearStart, String yearEnd, boolean flag){
		Vector<TotalSalaryYear> list = new Vector<TotalSalaryYear>();
		try {
			Login.conn.setAutoCommit(false);
			CallableStatement cst = Login.conn.prepareCall("{call sp_ListTSY(?,?,?,?)}");
			cst.setInt(1, Main.comId);
			cst.setString(2, yearStart);
			cst.setString(3, yearEnd);
			cst.setBoolean(4, flag);
			ResultSet rs = cst.executeQuery();
			while(rs.next()){
				TotalSalaryYear tsy = new TotalSalaryYear();
				tsy.setYear(rs.getString("sYear"));
				tsy.setTotalSalary(rs.getInt("TotalSalary"));
				list.add(tsy);
			}
			Login.conn.commit();
		} catch (SQLException e) {
			list = null;
			e.printStackTrace();
		}
		return list;
	}
	
}
